﻿-- =============================================
-- Author:		Jon Stocksdale
-- Create date: May 13, 2016
-- Description:	Given a particular TimeDiv ShortName, returns the Highest related super timediv
--               (i.e. T1(16) --> 2016-17)
-- =============================================
CREATE FUNCTION [dbo].[GetHighestTimeDiv]
(
	-- Add the parameters for the function here
	@EvalTimeDiv nvarchar(10),
	@UserEntityID int = 0,
	@InstitutionID int = 0
)
RETURNS nvarchar(10)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @RetTimeDiv nvarchar(10), @SuperTimeDivID int
	SET @RetTimeDiv=NULL

	IF (@EvalTimeDiv='' OR @EvalTimeDiv LIKE 'CURRENT%')
	SELECT TOP 1 @EvalTimeDiv=ShortName FROM dbo.TimeDivisions WHERE StartDate < SYSDATETIME() AND EndDate > SYSDATETIME()

	WHILE @RetTimeDiv is NULL BEGIN
		SELECT @SuperTimeDivID=SuperTimeDivID FROM TimeDivisions WHERE ShortName=@EvalTimeDiv
		IF @SuperTimeDivID is null
			SET @RetTimeDiv=@EvalTimeDiv
		ELSE
			SELECT @EvalTimeDiv=ShortName FROM TimeDivisions WHERE TimeDivID=@SuperTimeDivID
	END

	-- Return the result of the function
	RETURN @RetTimeDiv

END